﻿using System;
using System.Collections.Generic;
using System.Text;
using DataAccess;
using Entities;

namespace Logic
{
	public class SmartlistBL
	{
		public bool Save(Smartlist entity)
		{
			SmartlistDAL dal = new SmartlistDAL();
			return dal.Save(entity);
		}

		public bool Update(Smartlist entity)
		{
			SmartlistDAL dal = new SmartlistDAL();
			return dal.Update(entity);
		}

        public bool UpdateName(Smartlist entity)
        {
            SmartlistDAL dal = new SmartlistDAL();
            return dal.UpdateName(entity);
        }

		public bool Delete(Smartlist entity)
		{
			SmartlistDAL dal = new SmartlistDAL();
			return dal.Delete(entity);
		}

		public List<Smartlist> List()
		{
			SmartlistDAL dal = new SmartlistDAL();
			return dal.List();
		}

		public string ParseSmartListCriteria(Smartlist entity)
		{
			string[] ArrayCriteria = entity.StringCriteria.Split(';');
			string[] SQLParameters = new string[ArrayCriteria.Length];
			List<string> genresin = new List<string>();
			List<string> genresnotin = new List<string>();

			string SQL = "";

			int notdistinct = 0;

			string strOperator = " AND";
			string strCriteria = " = ";
			string strField = "movie_title";
			string strValue = "";

			for (int i = 0; i < ArrayCriteria.Length; i++)
			{
				string[] Parameters = ArrayCriteria[i].Split('|');

				switch (Parameters[0])
				{
					case "movie_title":
						strField = "movie_title";
						break;
					case "movie_genre":
						strField = "movie_genre";
						break;
					case "movie_director":
						strField = "movie_director";
						break;
					case "movie_year":
						strField = "movie_year";
						break;
					case "movie_rating":
						strField = "movie_rating";
						break;
				}

				switch (Parameters[1])
				{
					case "LIKE":
						if (strField != "movie_genre")
						{
							strCriteria = " LIKE ";
							strValue = "%" + Parameters[2] + "%";
						}
						else
						{
							strCriteria = " IN ";
							genresin.Add(Parameters[2]);
							strValue = Parameters[2];
						}
						break;
					case "NOT LIKE":
						if (strField != "movie_genre")
						{
							strCriteria = " NOT LIKE ";
							strValue = "%" + Parameters[2] + "%";
						}
						else
						{
							strCriteria = " NOT IN ";
							genresnotin.Add(Parameters[2]);
							strValue = Parameters[2];
						}
						break;
					case "=":
						if (strField != "movie_genre")
						{
							strCriteria = " = ";
							strValue = Parameters[2];
						}
						else
						{
							strCriteria = " IN ";
							genresin.Add(Parameters[2]);
							strValue = Parameters[2];
						}
						break;
					case "!=":
						if (strField != "movie_genre")
						{
							strCriteria = " != ";
							strValue = Parameters[2];
						}
						else
						{
							strCriteria = " NOT IN ";
							genresnotin.Add(Parameters[2]);
							strValue = Parameters[2];
						}
						break;
					case "<":
						strCriteria = " < ";
						strValue = Parameters[2];
						break;
					case ">":
						strCriteria = " > ";
						strValue = Parameters[2];
						break;
					case ">=":
						strCriteria = " >= ";
						strValue = Parameters[2];
						break;
					case "<=":
						strCriteria = " <= ";
						strValue = Parameters[2];
						break;
				}

				switch (Parameters[3])
				{
					case "AND":
						strOperator = " AND ";
						break;
					case "OR":
						if (strField == "movie_genre")
						{
							notdistinct++;
						}
						strOperator = " OR ";
						break;
				}

				if (i != ArrayCriteria.Length - 1)
				{
					if (strField == "movie_genre")
						SQLParameters[i] = "";
					else
						SQLParameters[i] = strField + strCriteria + "\"" + strValue + "\"" + strOperator;
				}
				else
					if (strField == "movie_genre")
						SQLParameters[i] = "";
					else
						SQLParameters[i] = strField + strCriteria + "\"" + strValue + "\"";
			}


			//Initializing SQL statement
			if (entity.StringCriteria.Contains("movie_genre"))
				SQL = "SELECT movies.* FROM movies INNER JOIN relationships ON (movies.movie_id=relationships.movie_id) WHERE relationships.relationship_type='genre' AND ";
			else
				SQL = "SELECT * FROM movies WHERE ";

			List<string> strgenres = new List<string>();

			//Defining genres allowed or forbidden
			if (genresin.Count > 0)
				strgenres.Add("relationships.relationship_element IN (" + String.Join(",", genresin.ToArray()) + ")");

			if (genresnotin.Count > 0)
				strgenres.Add("relationships.movie_id NOT IN (SELECT relationships.movie_id FROM relationships INNER JOIN movies ON (relationships.movie_id=movies.movie_id)  WHERE relationships.relationship_type='genre' AND relationships.relationship_element IN (" + String.Join(",", genresnotin.ToArray()) + "))");
			
			string conditionGenres = String.Join(" AND ", strgenres.ToArray());

			//Building SQL
			if (entity.StringCriteria.Contains("movie_genre"))
			{
				return SQL + String.Join(" ", SQLParameters) + conditionGenres + " GROUP BY movies.movie_id HAVING COUNT(DISTINCT relationships.relationship_element)=" + (genresin.Count-notdistinct);
			}
			else
				return SQL + String.Join(" ", SQLParameters);
		}

		public void SetStringCriteria(Smartlist entity)
		{
			string[] stringcriteria = new string[entity.Criteria.Count];

			for (int i = 0; i < entity.Criteria.Count; i++)
			{
				string field = "";
				string condition = "";
				string _operator = "";

				switch (entity.Criteria[i].Field)
				{
					case "Title":
						field = "movie_title";
						break;
					case "Genre":
						field = "movie_genre";
						break;
					case "Director":
						field = "movie_director";
						break;
					case "Year":
						field = "movie_year";
						break;
					case "Rating":
						field = "movie_rating";
						break;
				}

				switch (entity.Criteria[i].Condition)
				{
					case "contains":
						condition = "LIKE";
						break;
					case "does not contain":
						condition = "NOT LIKE";
						break;
					case "is":
						condition = "=";
						break;
					case "is not":
						condition = "!=";
						break;
					case "on":
						condition = "=";
						break;
					case "not on":
						condition = "!=";
						break;
					case "before":
						condition = "<";
						break;
					case "after":
						condition = ">";
						break;
					case "greater":
						condition = ">";
						break;
					case "greater or equal":
						condition = ">=";
						break;
					case "less":
						condition = "<";
						break;
					case "less or equal":
						condition = "<=";
						break;
					case "equals":
						condition = "=";
						break;
				}
				switch (entity.Criteria[i].Operator)
				{
					case "AND":
						_operator = "AND";
						break;
					case "OR":
						_operator = "OR";
						break;
				}
				stringcriteria[i] = String.Format("{0}|{1}|{2}|{3}", new string[] { field, condition, entity.Criteria[i].Value, _operator });
			}

			entity.StringCriteria = String.Join(";", stringcriteria);
		}

		public List<Movie> GetMovies(Smartlist entity)
		{
			string SQL = ParseSmartListCriteria(entity);

			MovieDAL mdal = new MovieDAL();
			return mdal.List(SQL);
		}
	}
}